<?php
require_once('Trip.class.php');
require_once('Contact.class.php');

/**
 * Implements the HelloRoad webservice to share data with Android application
 *
 * @author adrien
 */
class HelloRoad
{
	private $_connection = null;

	/**
	 * Initialize the connection to the DB
	 *
	 */
	public function __construct()
	{
		$this->_connection = mysql_connect('localhost', 'root', '') or
		  die("Error while connecting to original MySQL");
		mysql_select_db('helloroad');
	}


	/**
	 * Performs the registration
	 *
	 * @param   string  $username
	 * @param   string  $password
	 * @return  integer
	 */
	public function doRegister($username, $password)
	{
	   mysql_query("INSERT INTO user (`id`, `login`, `password`, `number`)
	       VALUES ('0', '{$username}', '{$password}', '0')");

	  return mysql_insert_id();
	}


	/**
	 * Check if username/password are correct
	 *
	 * @param   string  $username
	 * @param   string  $password
	 * @return  integer
	 */
	public function doLogin($username, $password)
	{
		$user = $this->_selectObject("SELECT *
                		              FROM user
                		              WHERE login = '{$username}'
									  AND password = '{$password}'
                	                  LIMIT 1");

		if ($user) {
			return $user->id;
		}
		else {
			return false;
		}
	}


	/**
	 * Retrieve data about trip $id
	 *
	 * @param   integer $id
	 * @return  Trip
	 */
	public function doGetTripInfo($id)
	{
		$trip = $this->_selectObject("SELECT  trip.id AS tripid,
		                                      l1.name AS departure,
		                                      l2.name AS arrival,
		                                      trip.driver_id AS driver,
		                                      trip.user_id AS user,
		                                      trip.date   AS date,
		                                      trip.time   AS time,
		                                      f.monday    AS mo,
		                                      f.tuesday   AS tu,
		                                      f.wednesday AS we,
		                                      f.thursday  AS th,
		                                      f.friday    AS fr,
		                                      f.saturday  AS sa,
		                                      f.sunday    AS su
                                        FROM `trip`
                                        LEFT JOIN location l1 ON trip.departure_id=l1.id
                                        LEFT JOIN location l2 ON trip.arrival_id=l2.id
                                        LEFT JOIN frequency f ON f.trip_id=trip.id
                                        WHERE trip.id={$id};");
		if (! $trip) {
			return null;
		}
		else {
		    $oTrip = new Trip($id, $trip->departure, $trip->arrival);
		    $oTrip->date          = $trip->date;
		    $oTrip->driver_id     = $trip->driver;
		    $oTrip->time          = $trip->time;
		    $oTrip->monday        = $trip->mo;
		    $oTrip->tuesday       = $trip->tu;
		    $oTrip->wednesday     = $trip->we;
		    $oTrip->thursday      = $trip->th;
		    $oTrip->friday        = $trip->fr;
		    $oTrip->saturday      = $trip->sa;
		    $oTrip->sunday        = $trip->su;

			return $oTrip;
		}
	}


	/**
	 * Retrieve user $id profile as Contact object
	 *
	 * @param  integer $id
	 * @return Contact
	 */
	public function doGetUserInfo($id)
	{
		$user = $this->_selectObject("SELECT * FROM user WHERE id = '{$id}'");
		$object = new Contact($id, $user->login, $user->firstname, $user->lastname, $user->number, $user->address);
		return $object;
	}


	/**
	 * Retrieve user profile as a Contact object, according to its $login
	 *
	 * @param  string $login
	 * @param  return Contact
	 */
    public function doGetUserByLogin($login)
    {
        $user = $this->_selectObject("SELECT * FROM user WHERE login = '{$login}'");
        $object = new Contact($user->id, $user->login, $user->firstname, $user->lastname, $user->number, $user->address);
        return $object;
    }


    /**
     * Update informations of user $id
     *
     * @param   integer $id
     * @param   string  $first
     * @param   string  $last
     * @param   string  $number
     * @param   string  $address
     */
	public function doUpdateUserInfo($id, $first, $last, $number, $address)
	{
		mysql_query("UPDATE user SET
		              firstname   = '{$first}',
		              lastname    = '{$last}',
		              address     = '{$address}',
		              number      = '{$number}'
		              WHERE id = '{$id}'");
	}


	/**
	 * Register a new Trip. $from and $to are towns
	 *
	 * @param string   $from
	 * @param string   $to
	 * @param integer  $user_id who registers the Trip
	 * @param string   $date, in mm-dd-yyyy format
	 * @return integer ID
	 */
	public function doAddTrip($from, $to, $user_id, $date, $time, $driver_id, $monday, $tuesday, $wednesday, $thursday, $friday, $saturday, $sunday)
	{
		$o_from = $this->_selectObject("SELECT id FROM location WHERE name='{$from}'");
		$o_to 	= $this->_selectObject("SELECT id FROM location WHERE name='{$to}'");

		if (! $o_from) {
			mysql_query("INSERT INTO location (`name`) VALUES ('{$from}')");
			$from_id = mysql_insert_id();
		}
		else {
			$from_id = $o_from->id;
		}

		if (! $o_to) {
			mysql_query("INSERT INTO location (`name`) VALUES ('{$to}')");
			$to_id = mysql_insert_id();
		}
		else {
			$to_id = $o_to->id;
		}

		mysql_query("INSERT INTO trip (`departure_id`, `arrival_id`, `user_id`, `date`, `time`, `driver_id`)
		                      VALUES ('{$from_id}', '{$to_id}', '{$user_id}', '{$date}', '{$time}', '{$driver_id}')");

		$trip_id = mysql_insert_id();


		if (($trip_id > 0) && (! $date))
		{
            mysql_query("INSERT INTO frequency (`monday`, `tuesday`, `wednesday`, `thursday`, `friday`, `saturday`, `sunday`, `trip_id`)
                        VALUES ('{$monday}', '{$tuesday}', '{$wednesday}', '{$thursday}', '{$friday}', '{$saturday}', '{$sunday}', '{$trip_id}')");
		}

		return $trip_id;
	}


	/**
	 * Add $contactId as a new contact of $userId
	 *
	 * @param  integer $userId
	 * @param  integer $contactId
	 */
	public function doAddContact($userId, $contactId)
	{
        mysql_query("INSERT INTO contact VALUES (0, '{$userId}', '{$contactId}')");
	}


	/**
	 * Retrieve the list of contacts of $userId
	 *
	 * @param  integer $userId
	 * @return array of Contact
	 */
	public function doGetListOfContacts($userId)
	{
		$q = "SELECT * FROM contact WHERE user_id='{$userId}'";
		$r = mysql_query($q);
		$result = array();

		while ($o = mysql_fetch_object($r))
		{
			$firstname = null;
			$lastname = null;
			$address = null;
			$contact = mysql_fetch_object(mysql_query("SELECT * FROM user WHERE id = '{$o->user_contact_id}'"));
			if (isset($contact->firstname))
				$firstname = $contact->firstname;
			if (isset($contact->lastname))
				$lastname = $contact->lastname;
			if (isset($contact->address))
				$address = $contact->address;
			if (isset($contact->number))
				$number = $contact->number;
			else
				$number = $contact->id * 12345;
			$newcontact = new Contact($contact->id, $contact->login, $firstname, $lastname, $number, $address);
			$result[] = $newcontact;
		}

		return $result;
	}

	public function doGetLocations()
	{
		$q = "SELECT name FROM location";
		$r = mysql_query($q);
		$result = array();
		
		while ($location = mysql_fetch_object($r))
			$result[] = $location;
		return $result;
	}
	
	
	/**
	 * Retrieve the list of trips of the user $userId
	 *
	 * @param  integer $userId
	 * @return array of Trip
	 */
	public function doGetListOfTrips($userId)
	{
        $q = "SELECT  trip.id AS tripid,
                      l1.name AS departure,
                      l2.name AS arrival,
                      trip.driver_id AS driver,
                      trip.user_id AS user,
                      trip.date   AS date,
                      trip.time   AS time,
                      f.monday    AS mo,
                      f.tuesday   AS tu,
                      f.wednesday AS we,
                      f.thursday  AS th,
                      f.friday    AS fr,
                      f.saturday  AS sa,
                      f.sunday    AS su
                FROM `trip`
                LEFT JOIN location l1 ON trip.departure_id=l1.id
                LEFT JOIN location l2 ON trip.arrival_id=l2.id
                LEFT JOIN frequency f ON f.trip_id=trip.id
                WHERE trip.user_id={$userId};";

		$r = mysql_query($q);
		$result = array();

		while ($trip = mysql_fetch_object($r))
		{
            $oTrip = new Trip($trip->tripid, $trip->departure, $trip->arrival);
            $oTrip->date          = $trip->date;
            $oTrip->driver_id     = $trip->driver;
            $oTrip->time          = $trip->time;
            $oTrip->monday        = $trip->mo;
            $oTrip->tuesday       = $trip->tu;
            $oTrip->wednesday     = $trip->we;
            $oTrip->thursday      = $trip->th;
            $oTrip->friday        = $trip->fr;
            $oTrip->saturday      = $trip->sa;
            $oTrip->sunday        = $trip->su;

			$result[] = $oTrip;
		}

		return $result;
	}

	/**
	 * Perform a search
	 *
	 * @param  string  $from : departure place
	 * @param  string  $to   : arrival place
	 * @param  string  $date : date of trip
	 * @param  string  $time : time of trip
	 */
	public function doSearch($from, $to, $date, $time)
	{
       $q = "SELECT
                    trip.id AS tripid,
                    trip.date AS date,
                    dep.name AS departure,
                    arr.name AS arrival,
                    trip.driver_id AS driver,
                    trip.user_id AS user,
                    trip.date   AS date,
                    trip.time   AS time,
                    f.monday    AS mo,
                    f.tuesday   AS tu,
                    f.wednesday AS we,
                    f.thursday  AS th,
                    f.friday    AS fr,
                    f.saturday  AS sa,
                    f.sunday    AS su
                FROM trip
                    LEFT JOIN location dep ON trip.departure_id = dep.id
                    LEFT JOIN location arr ON trip.arrival_id = arr.id
                    LEFT JOIN frequency f ON f.trip_id=trip.id
                WHERE
                    dep.name LIKE '$from'
                    AND arr.name LIKE '$to'";

       if ($date)
       {
           $q .= " AND trip.date = '$date'";
       }

        $r = mysql_query($q);
        $result = array();

        while ($trip = mysql_fetch_object($r))
        {
            $oTrip = new Trip($trip->tripid, $trip->departure, $trip->arrival);
            $oTrip->date          = $trip->date;
            $oTrip->driver_id     = $trip->driver;
            $oTrip->time          = $trip->time;
            $oTrip->monday        = $trip->mo;
            $oTrip->tuesday       = $trip->tu;
            $oTrip->wednesday     = $trip->we;
            $oTrip->thursday      = $trip->th;
            $oTrip->friday        = $trip->fr;
            $oTrip->saturday      = $trip->sa;
            $oTrip->sunday        = $trip->su;

            $result[] = $oTrip;
        }

        return $result;
	}

    /**
     * Perform a search
     *
     * @param  string  $from : departure place
     * @param  string  $to   : arrival place
     * @param  string  $days : date of trip
     * @param  string  $time : time of trip
     */
    public function doSearchByDays($from, $to, $mo, $tu, $we, $th, $fr, $sa, $su, $time)
    {
       $q = "SELECT
                    trip.id AS tripid,
                    trip.date AS date,
                    dep.name AS departure,
                    arr.name AS arrival,
                    trip.driver_id AS driver,
                    trip.user_id AS user,
                    trip.date   AS date,
                    trip.time      AS time,
                    f.monday    AS mo,
                    f.tuesday   AS tu,
                    f.wednesday AS we,
                    f.thursday  AS th,
                    f.friday    AS fr,
                    f.saturday  AS sa,
                    f.sunday    AS su
                FROM trip
                    LEFT JOIN location dep ON trip.departure_id = dep.id
                    LEFT JOIN location arr ON trip.arrival_id = arr.id
                    LEFT JOIN frequency f ON f.trip_id=trip.id
                WHERE
                    dep.name  '$from'
                    AND arr.name LIKE '$to'";

        if ($mo)
        {
           $q .= " AND f.mo = '$mo'";
        }

        if ($tu)
        {
           $q .= " AND f.tu = '$tu'";
        }

        if ($we)
        {
           $q .= " AND f.we = '$we'";
        }

        if ($th)
        {
           $q .= " AND f.th = '$th'";
        }

        if ($fr)
        {
           $q .= " AND f.fr = '$fr'";
        }

        if ($sa)
        {
           $q .= " AND f.sa = '$sa'";
        }

        if ($su)
        {
           $q .= " AND f.su = '$su'";
        }

        $r = mysql_query($q);
        $result = array();

        while ($trip = mysql_fetch_object($r))
        {
            $oTrip = new Trip($trip->tripid, $trip->departure, $trip->arrival);
            $oTrip->date          = $trip->date;
            $oTrip->driver_id     = $trip->driver;
            $oTrip->time          = $trip->time;
            $oTrip->monday        = $trip->mo;
            $oTrip->tuesday       = $trip->tu;
            $oTrip->wednesday     = $trip->we;
            $oTrip->thursday      = $trip->th;
            $oTrip->friday        = $trip->fr;
            $oTrip->saturday      = $trip->sa;
            $oTrip->sunday        = $trip->su;

            $result[] = $oTrip;
        }

        return $result;
    }

	/*
	 * Returns the first instance of selected rows
	 */
	private function _selectObject($query)
	{
		return mysql_fetch_object(mysql_query($query));
	}
}
?>